﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.FXModel
{
    public partial class FXFinanceLeaseLPayLFMee : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "融资租赁进度表_首付及管理费.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "融资首付及管理费";
            }
        }

        protected string DateFrom
        {
            get
            {
                DateTime dt = DateTime.Now; //当前时间
                return GetRequestValue<string>("DateFrom", DateTime.Now.ToString("yyyy-MM-01"));
            }
        }
        protected string DateTo
        {
            get
            {
                DateTime dt = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-01")); //当前时间

                return GetRequestValue<string>("DateTo", dt.ToString("yyyy-MM-dd 23:59:59"));

            }
        }
        /// <summary>
        /// 当前日期
        /// </summary>
        protected string CurrDate
        {
            get
            {
                return DateTime.Now.ToString("yyyy年MM月dd日");
            }
        }
        protected string GetSQLWhere()
        {
            //营业经理权限 2014-02-21
            string agentList = base.FTradeAgentPowerSqlWhere(UserId);
            string wheresql = string.IsNullOrEmpty(agentList) ? " 1=1 " : string.Format(" AliasName in ({0})", agentList);

           // wheresql += " and WeiShouKuanXiang>0";

            return wheresql;
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            DataTableCollection dts = FXFinanceLeaseController.ReportGetLPayLFMee(GetSQLWhere());
            // throw new Exception("xx");
            //所有有未收款项的经销商
            DataTable allAgentlist = dts[0];

            DataTable allDetail = dts[1];//
            DataTable sboagentlist = SBOController.GetAgentList();//所有经销商列表
            InitializeWorkbook();

            string AliasName = "";
            int sheetindex = 1;//从第二个工作表开始
            foreach (DataRow dr in allAgentlist.Rows)
            {
                AliasName = dr["AliasName"].ToString();
                DataTable detaildt = new DataTable();

                switch (AliasName)
                {
                    case "湖南金力源":
                        detaildt = allDetail.Select("AliasName in('" + AliasName + "','湖南金土地')").CopyToDataTable();
                        break;
                    case "青岛金吉宇":
                        detaildt = allDetail.Select("AliasName in('" + AliasName + "','青岛吉宇')").CopyToDataTable();
                        break;
                    default:
                        detaildt = allDetail.Select("AliasName='" + AliasName + "'").CopyToDataTable();
                        break;
                }

                //if (AliasName == "湖南金力源")
                //{
                //    detaildt = allDetail.Select("JingXiaoShang in('" + AliasName + "','湖南金土地')").CopyToDataTable();
                //}
                //else
                //{
                //    detaildt = allDetail.Select("JingXiaoShang='" + AliasName + "'").CopyToDataTable();
                //}
                ISheet sheet2 = hssfworkbook.CloneSheet(0);
                hssfworkbook.SetSheetName(sheetindex, AliasName);
                //经销商全称                
                DataRow[] drs = sboagentlist.Select(string.Format(" AliasName ='{0}'", AliasName.Trim()));
                string cardname = drs[0]["CardName"].ToString();

                if (detaildt.Rows.Count <= 0)
                    continue;

                WriteSheet(sheet2, detaildt, cardname);

                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }
            //隐藏
            hssfworkbook.RemoveSheetAt(0);
            //
            WriteToFile();
        }

        private void WriteSheet(ISheet sheet2, DataTable exportDT, string cardname)
        {

            //设置文号（NO.）：
            sheet2.GetRow(8).GetCell(5).SetCellValue(string.Format("FG{0}-01", DateTime.Now.ToString("yyyyMMdd")));
            //
            sheet2.GetRow(5).GetCell(1).SetCellValue(cardname);
            //第10行 处理
            //    截止2015年4月7日，贵司应付用户融资首付总额为    元.管理费总额为   元 （明细见附表），提醒贵司及时支付，以免影响后期融资业务办理。
            string weiFuLPay = Convert.ToDecimal(exportDT.Compute("SUM(LPay)", "")).ToString("N2");//融资首付总额
            string weiFuLMFee = Convert.ToDecimal(exportDT.Compute("SUM(LMFee)", "")).ToString("N2");//管理费总额
            //
            ICell richTextCell = sheet2.GetRow(9).GetCell(0);
            string richText = string.Format("    截止{0}，贵司应付用户融资首付总额为{1}元.管理费总额为{2}元 （明细见附表），提醒贵司及时支付，以免影响后期融资业务办理。", CurrDate, weiFuLPay, weiFuLMFee);
            HSSFRichTextString richtextObj = new HSSFRichTextString(richText);
            //IFont fontStyleRich = hssfworkbook.CreateFont();
            //fontStyleRich.Underline = (byte)FontUnderlineType.SINGLE;
            //fontStyleRich.Boldweight = short.MaxValue;//加粗
            //fontStyleRich.FontHeightInPoints = 12;
            //12号字体样式
            IFont fontStyleRich2 = hssfworkbook.CreateFont();
            fontStyleRich2.FontHeightInPoints = 12;

            richtextObj.ApplyFont(0, richText.Length, fontStyleRich2);
            ////1.加粗位置处理
            //int boldStart = string.Format("       截止{0},贵司整机欠款总额为", CurrDate).Length; ;
            //int boldEnd = string.Format("       截止{0},贵司整机欠款总额为{1}万元", CurrDate, WeiShouKuanXiang).Length;
            //richtextObj.ApplyFont(boldStart, boldEnd, fontStyleRich);
            //boldStart = string.Format("       截止{0},贵司整机欠款总额为{1}万元(明细见附表),", CurrDate, WeiShouKuanXiang).Length;
            //boldEnd = string.Format("       截止{0},贵司整机欠款总额为{1}万元(明细见附表),其中本月应还款总额为{2}万元,提醒贵司到期按时还款", CurrDate, WeiShouKuanXiang, daoqijine).Length;
            //richtextObj.ApplyFont(boldStart, boldEnd, fontStyleRich);
            richTextCell.SetCellValue(richtextObj);
            //HSSFRichTextString
            //STRING:附表：整机欠款明细（截止2013年07月09日）：
            richText = string.Format("附表：首付款及管理费明细（截止{0}）", CurrDate);
            //ICellStyle style12 = hssfworkbook.CreateCellStyle();
            //style12.SetFont(fontStyleRich2);
            ICell cell1 = sheet2.GetRow(10).GetCell(0); ;
            //cell1.CellStyle = style12;
            cell1.SetCellValue(richText);
            //列标题
            //sheet2.GetRow(13).GetCell(3).SetCellValue(string.Format("贵司{0}月份应还款",Convert.ToDateTime(DateTo).ToString("MM")));
            //设置其它信息
            sheet2.GetRow(19).GetCell(4).SetCellValue(CurrDate);
            sheet2.GetRow(20).GetCell(0).SetCellValue(string.Format("-------------------------请填下表于{0}月25日前回传：0592-6292200----------------------", Convert.ToDateTime(DateTo).ToString("MM")));
           
            sheet2.GetRow(24).GetCell(0).SetCellValue(string.Format("{0}（盖章）", cardname));
            sheet2.GetRow(25).GetCell(4).SetCellValue(string.Format("{0}   日", Convert.ToDateTime(DateTo).ToString("yyyy年MM月")));
            //开始FOR各合同信息
            if (exportDT.Rows.Count > 2)
            {
                HSSFRow 源格式行 = (HSSFRow)sheet2.GetRow(12);
                MyInsertRow((HSSFSheet)sheet2, 13, exportDT.Rows.Count - 2, 源格式行);
            }
            int rowindexBase = 12;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            ICellStyle style12 = hssfworkbook.CreateCellStyle();
            style12.SetFont(fontStyleRich2);
            //水平对齐  
            style12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
            //垂直对齐  
            style12.VerticalAlignment = VerticalAlignment.CENTER;
            //自动换行  
            style12.WrapText = true;

            foreach (DataRow dr in exportDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet2.GetRow(rowReal);
                //合并处理
                //  sheet2.AddMergedRegion(new CellRangeAddress(rowReal, rowReal, 4, 5));
                // row1.GetCell(4).CellStyle = style12;

                row1.GetCell(0).SetCellValue(dr["CustomerName"].ToString());
                row1.GetCell(1).SetCellValue(dr["SerialNumber"].ToString());
                row1.GetCell(2).SetCellValue(Convert.ToDouble(dr["LTotalPrice"].ToString()));
                row1.GetCell(3).SetCellValue(dr["PassDate"].ToString());
               // row1.GetCell(4).SetCellValue(Convert.ToDouble(dr["daoqijine"].ToString()));
                row1.GetCell(5).SetCellValue(Convert.ToDouble(dr["LPay"].ToString()));
                row1.GetCell(6).SetCellValue(Convert.ToDouble(dr["LMFee"].ToString()));
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

        }




        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls", DateTime.Now.ToString("yyyy年MM月"), "融资首付及管理费表").Trim();
        }
    }
}